Computerized Investing - Spreadsheet Collection/Spreadsheet Collection.iso/pc/ibm11/bond.doc

Text File | 1991-07-11 | 13.4 KB | 304 lines

Using a Spreadsheet to Determine Bond Price Volatility
By Fred Shipley, Ph.D.
Computerized Investing, September/October 1988

     The volatility in the stock market over the past year
caused many investors to consider fixed-income investments
as a means of reducing variability in their portfolios.
For some investors, however, the fairly steady climb in
interest rates since the market crash has led to an
erosion in portfolio values.  To help you evaluate the
volatility inherent in fixed-income investments, we are
presenting in this issue a spreadsheet template that
allows you to calculate bond values based on knowledge of
the coupon rate, the length of time to maturity and the
current market rate of interest.  (All of these terms and
the basics of bond pricing are explained later.)  In
addition, you will be able to see how bond prices can
change with changes in market interest rates.  In
particular, you can examine (with a little extra work) the
effects of changing interest rates on the total value of a
bond portfolio, including a bond mutual fund.

     Fixed-income securities are investments that make a
promise to pay a specific dollar return for some fixed
period of time.  We usually refer to longer-term fixed-
income investments as bonds, and problems of volatility
are more relevant to intermediate- and long-term
investments.  Short-term fixed-income investments are
those with a maturity of no more than one year, and they
are usually called money market investments.
Intermediate-term investments have maturities from one to
seven years, and long-term bonds have maturities of more
than seven years.  The longest term bonds rarely have
maturities of more than 30 years.  These definitions
correspond with the SEC requirements for descriptions
applied to bond mutual funds.  Since the dollar payments
are usually fixed when the bonds are first issued, a
change in market interest rates will cause a change in the
market value of the bonds.

     The first rule of bond price changes is the inverse
relation between market values and market interest rate
changes.  If market interest rates go up, the value of the
fixed interest and principal payments you expect to
receive goes down.  Conversely, if interest rates fall,
the value of the bonds will increase.  Put simply, the
bond market rallies when interest rates fall.

     Two factors are of primary importance in determining
the degree of volatility or price sensitivity of a bond
investment -- the coupon rate and the time to maturity.
The coupon rate is the interest rate on the bond that is
established at the time of issuance.  The higher the
coupon rate, the less the value of the bond will change in
response to interest rate changes.  Then again, the lower
the coupon rate, the more the value of the bond will vary
with changes in market interest rates.  Essentially, the
higher coupon provides more cash flow and provides it
earlier, so it acts as an anchor reducing the impact of
interest rate changes on price changes.

     Maturity is the second important factor affecting the
degree of price volatility.  The longer the time before a
bond matures, the more sensitive its value will be to
interest rate changes.  Since bond values change inversely
to the change in interest rates, a long-term, low-coupon-
rate bond will suffer a very great decrease in value when
interest rates increase; a short-term, high-coupon rate
bond will change relatively little in value.  The longer-
term maturity means that it takes longer for investors to
recover their initial investment and so the market value
of the bond is more sensitive to changes in market
interest rates.

     Frequently maturities and coupon rates available in
the market have offsetting effects.  For example, from the
late 1970s to the early 1980s when interest rates were
quite high, newly issued bonds carried high coupon rates
(which mean less volatility) and had longer maturities
(which means greater volatility) than bonds issued several
years previously.  Consequently it is important to have a
measure of price sensitivity to interest rate changes that
takes account of both maturity and coupon effects.
Financial analysts use such a measure of price volatility
-- it is known as duration.  Duration is a weighted-
average time measure.  Using present value weightings, in
which later cash flows are less valuable than earlier cash
flows, it indicates a weighted-average maturity.  Because
of this weighting scheme, we can say that, regardless of
the coupon rate and the maturity of different bonds, we
can say that a bond with longer duration will be more
volatile than one with the shorter duration.  Thus
duration is directly related to price volatility caused by
interest rate changes.   Unfortunately, duration is a
fairly complicated concept, both in terms of intuition and
mathematics.


Basics of Bond Valuation

     The value of a bond is simply the present value of
the cash payments through maturity to the bondholder.  In
the case of a bond with a fixed coupon rate, this stream
of cash payments is relatively easy to determine.  There
may be uncertainty whether the issuer will in fact make
all the payments as required, but at least there is a
legal commitment to make certain payments -- interest at
specified intervals and repayment of principal at
maturity.  The only other ingredient necessary to complete
the valuation is the appropriate market rate of interest.
This market rate of interest is the current return being
offered on bonds of similar risk and maturity.  We won't
worry in detail about that now, since we are simply
interested in understanding the effect of changing rates
on value.  All we must do is use an estimate of the future
level of rates.  This can be something we get from reading
an economic forecast in the paper or an estimate based on
our own evaluation of economic and market conditions.

     We will start by assuming that our valuation is done
at an interest payment date.  In the case of a typical
bond, this means that we will not receive another payment
for 6 months (since almost all corporate bonds make semi-
annual interest payments).

     Most spreadsheets provide a present value function
that is quite handy for this valuation.  The @PV function
in 1-2-3 computes the present value of a series of equal
payments if you specify the amount of the payment, the
appropriate interest rate to use and the number of periods
the payments will be made.  This @PV function, however,
only works for constant dollar payments, and a bond does
not quite have only constant dollar payments.  If you hold
the bond to maturity the last payment received will
include the repayment of principal as well as the final
payment of interest.  We can get around this problem by
treating the interest and principal payments separately.


Necessary Data Inputs for Bond Template

     The data necessary to compute duration is entered in
rows five through nine and includes the number of years to
maturity (15 1/2 years, for example is entered as 15.5),
the principal amount ($1,000, except in special cases),
the coupon rate (enter the rate as a decimal), and the
market interest rate (you can get a reasonable number to
use from Barron's Market Laboratory section if you know
the bond's rating).  The BONDIRR template deals with more
complicated situations in which your have to calculate
values in between interest payment dates.

     In rows 11 through 15, some useful values are
calculated.  The formula for determining the market value
is in cell C13.  The first part of this equation computes
the present value of the interest payments, the second
part computes the present value of the repayment of
principal.  (The present value function in 1-2-3 computes
the present value of a stream of payments; this is
somewhat different from commonly used terminology.)  It
might be tempting to simply refer to cell C11, where the
semi-annual interest payments are calculated, instead of
$C$7/2*$C$6 in the first part of the @PV function.  This
was not done however, because the formula is to create a
two-way data table.  Lotus allows only one input formula
for determining the values in a two-way data table and
making the reference to C11 there would result in all the
values staying at $1,000.  None of those values would
change no matter what you did to the data table.

Establishing a Data Table to Determine Price Sensitivity

     Rather than dealing with the complications of
duration now, the data table (sensitivity analysis)
capability of 1-2-3 was used to see the effect of changing
interest rates and maturity on values.  This is
particularly useful for dealing with portfolios of bonds,
as long as we can determine the average maturity and
average coupon on the portfolio.  We will deal with those
issues later.

     Setting up the data table was a two step process.
First a table was created for bond prices, then we that
table was used to make a table of percentage price
changes.  This latter table can be used to create some 1-
2-3 graphs illustrating the price sensitivity.

     The important things to remember in setting a data
table are that the formula used to create the values must
appear in the upper left hand corner, the first data input
values are those along the left hand side of the table,
and the second input values are along the top of the
table.  Finally, remember that the table range must
include the bordering rows and columns.

     If you change the input variables, you can update the
data table by simply pressing the F8 (table) key.  1-2-3
automatically updates the most recently created table.  If
you are using more than one data table, you will have to
manually recreate the older table.

     While the tables in the bond template are not very
big, it may take a while to recalculate, especially on a
regular IBM PC or clone.  More complicated data tables can
take 15 to 20 minutes or more to recalculate.  Be prepared
to deal with these delays.  It is better to make all the
changes you want to make before recalculating large data
tables.

     To simplify the evaluation of the degree of price
sensitivity, the first table of prices was used to a
separate table that gives percentage price changes.
Looking at percentage changes allows you to make easier
comparisons between bonds or bond portfolios of different
coupons and maturities.

Some Further Information about Duration

     Since duration indicates price sensitivity, we
included the formula to calculate in cell C14.  For any
coupon-bearing bond, duration is always less than the
bond's term to maturity and it tends to reach a limit,
which is related not to maturity, but rather to the
current market interest rate.  This limit is given by the
formula:

                                    1
     Duration Limit =    1  +  ----------
                                   YTM

          where:            YTM is the bond's market yield.

For the a bond with a market interest rate of 8%, this
formula gives us:

                                     1
     Duration Limit      =  1  +  ƒƒƒƒƒƒƒ
                                    .08

                         =  1  +  12.5

                         =  13.5 yrs.

     So a perpetual bond with an 8% coupon and 8% market
yield has a duration that is only 13.5 years.

     If you are evaluating a number of bonds or bond
portfolios, you may not want to create detailed price
sensitivity tables for each one.  In this case, using the
duration formula will enable you to get a rough feel for
the sensitivity.  You can then perform the more detailed
analysis on a smaller number of bonds.


Using Duration to Analyze Price Sensitivity

     While we know that longer duration means greater
price sensitivity to market interest rate changes, nothing
we have done so far indicates the magnitude of the effect.
For relatively small market interest rate changes, the
formula below is a useful approximation of the percentage
change in bond price:


Approximate                        Change in market rate
     %           =  -(Duration) x -----------------------
Price Change                         (1 + market rate)




Suppose we expect interest rates to increase from 8% to 9%.
According to the formula, the approximate percentage price change
would be

Approximate                    (.09 - .08)
     %             =   -(8.99) -----------
Price Change                    (1 + .08)

                   =  -(8.99) x (.0093)

                   =  -.0832 or

                   =  -8.32%

     From the bond template we can see that the actual
percentage price change is -8.14%.

     If interest rates were to decrease from 8% to 7%,
this approximation formula would give us an 8.32%
increase, whereas the actual percentage increase is 9.20%.
This illustrates the approximation that is part of this
formula, but it makes for some quick calculations.  This
also illustrates another important feature of bond price
sensitivity.  Changes in value are not symmetric for
market interest rate increases and decreases.  A 1%
increase in rates will always give a smaller percentage
decrease in value than the percentage increase in value
from a 1% decrease in rates.  In this way investors have a
little protection if they guess wrong about the direction
of changes in market rates.

(c) Copyright 1988 by the
American Association of Individual Investors